<?php

namespace app\common\service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use think\facade\Filesystem as NewFilesystem;

class Excel
{

    public function Filterate($excel_field = [], $field = '')
    {
        $newCols = [];
        $oldCols = [];
        foreach ($excel_field as $k => $v) {
            $oldCols[$v['field']] = $v;
        }
        $fields = explode(',', $field);
        foreach ($fields as $k => $v) {
            $t = explode('.', $v);
            if ($t[0] == 'a') {
                $str = $t[1];
            } else {
                $str = $t[0] . '__' . $t[1];
            }
            if (!empty($oldCols[$str])) {
                $newCols[] = $oldCols[$str];
            }
        }
        return $newCols;
    }

    public function Out($arr = [], $name = '')
    {
        $spreadsheet = new Spreadsheet();
        foreach ($arr as $key => $data) {
            $this->SheetExcel($spreadsheet, $key, $data);
        }
        ob_end_clean();
        ob_start();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $spreadsheet->setactivesheetindex(0);
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    public function SheetExcel($spreadsheet, $n, $data)
    {
        $setTitle = $data['title'];
        $cols = $data['cols'];
        $list = $data['list'];
        set_time_limit(0);
        $perSize = 1000;//每次导出的条数
        $pages = ceil(count($list) / $perSize); //循环多少次
        $heard = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
            'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE',
            'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR',
            'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        ];
        if ($n == 0) {
            $objActSheet = $spreadsheet->getActiveSheet();;//设置当前的活动sheet
            $sheet = $objActSheet;
        } else {
            $spreadsheet->createSheet();//创建sheet
            $objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
            $sheet = $spreadsheet->getActiveSheet($n);
        }

        $sheet->setTitle($setTitle);//设置工作表标题名称
        // 标题
        $sheet_end = 'B';
        foreach ($cols as $key => $value) {
            $objActSheet->setCellValue($heard[$key] . '1', $value['title'] . PHP_EOL . $value['field']);
            $objActSheet->getColumnDimension($heard[$key])->setAutoSize(true);
            $sheet_end = $heard[$key];
        }
        // 标题end

        $ii = 2;
        foreach ($list as $_v) {
            $j = 0;
            foreach ($_v as $_cell) {
                $urls = explode('http', $_cell);
                if (count($urls) == 1) {
                    $sheet->setCellValue($heard[$j] . $ii, $_cell);
                } else {
                    $sheet->setCellValue($heard[$j] . $ii, '查看');
                    $sheet->getCell($heard[$j] . $ii)->getHyperlink()->setUrl($_cell);
                }
                $j++;
            }
            $ii++;
        }
        $sheet->getDefaultRowDimension()->setRowHeight(30);
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000'],
                ],
                'inside' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000'],
                ]
            ],
            'alignment' => [
                'wrapText' => true,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, //垂直居中
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'E7E6E6',
                ]
            ],
        ];
        $index = $ii - 1;
        $sheet->getStyle('A1:' . $sheet_end . $index)->applyFromArray($styleArray)->getFont()->setSize(10);;// 外边框，内边框
    }


    public function Import($file)
    {
        $file_local = 'inexcel';
        $fileTmp = 'storage/' . NewFilesystem::disk('public')->putFile($file_local, $file);
        $fileTmp = str_replace('\\', '/', $fileTmp);
        $fileType = IOFactory::identify($fileTmp);
        $objReaderExcel = IOFactory::createReader($fileType);
        $objReaderExcel->setLoadSheetsOnly(true);
        $objExcel = $objReaderExcel->load($fileTmp);
        $sheet = $objExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $UpData = [];
        $cols = [];
        $DataField = [];
        if ($highestRow <= 1) {
            return ['code' => 203, 'msg' => '上传的文件没有数据', 'data' => []];
        }
        for ($i = 1; $i <= $highestRow; $i++) {
            for ($column = 'A'; $column <= $highestColumn; $column++) {
                if ($i == 1) {
                    $title = $objExcel->getActiveSheet()->getCell($column . $i)->getValue();
                    $result = explode("\n", preg_replace("/\r\n|\r|\n/", "\n", $title));
                    $DataField[$column] = !empty($result[1]) ? $result[1] : '';
                    $cols[] = [
                        'field' => $DataField[$column],
                        'title' => !empty($result[0]) ? $result[0] : '',
                        'minWidth' => 160
                    ];
                }
                if ($i != 1) {
                    $index = $i - 2;
                    $value = $objExcel->getActiveSheet()->getCell($column . $i)->getValue();
                    if (!empty($DataField[$column])) {
                        $UpData[$index][$DataField[$column]] = $value;
                    }
                }
            }
        }
        if (count($UpData) == 0) {
            return ['code' => 203, 'msg' => '上传的文件没有数据', 'data' => []];
        }
        $data['data'] = $UpData;
        $data['cols'] = $cols;
        return ['code' => 200, 'msg' => '数据读取成功', 'data' => $data];
    }

    /*生成导入模板*/
    public function Template($name = '', $dataTitle = [])
    {

        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $worksheet = $spreadsheet->getActiveSheet();
        $heard = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
            'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE',
            'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR',
            'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        ];
        // 标题
        foreach ($dataTitle as $key => $value) {
            $worksheet->setCellValue($heard[$key] . '1', $value['title'] . PHP_EOL . $value['field']);
            $worksheet->getColumnDimension($heard[$key])->setAutoSize(true);
        }
        $worksheet->getDefaultRowDimension()->setRowHeight(30);
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000'],
                ],
                'inside' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000'],
                ]
            ],
            'alignment' => [
                'wrapText' => true,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, //垂直居中
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'E7E6E6',
                ]
            ],
        ];
        $worksheet->getStyle('A1:' . $heard[count($dataTitle) - 1] . '1')->applyFromArray($styleArray)->getFont()->setSize(10);// 外边框，内边框
        $worksheet->freezePane('A2');
        ob_end_clean();
        ob_start();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '导入模板.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        unset($spreadsheet);
        exit;
    }
}